<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2019/1/2
 * Time: 14:12
 */

namespace app\console\controller;
use app\console\model\OrderGoods;
use app\console\model\Order;
use think\Controller;
use think\Db;
use think\Request;
use think\Session;
use app\console\model\RefundOrderAudit;
use think\Cache;

class Finance extends Controller
{
    protected $beforeActionList = [
        'shop_list'  =>[]
    ];

    public function shop_list()
    {
        $shop_list = Db::name('shop s')->field('id,shop_title')->select();
        $this->assign('shop_list', $shop_list);
    }
    /**
     * 财务管理
     */
    public function index($pa = 10)
    {

        $param = Request::instance()->param();
        $level = \session('role_id');//当前登录的管理员界别 5 7 8 9 10
        $page = Request::instance()->param('page',10);
        $where = [];
        $auding_status = Session::get('auding_status');
        if($auding_status == 1){
            $where['shop_id'] = ['eq',\session('shop_id')];
        }

        if (!empty($param['status'])){
            if($param['status'] == 2 || $param['status'] == 6){
                $where['o.status'] = ['in', $this->status];
            }else{
                $where['o.status'] = ['eq', $param['status']];
            }
        }


        if(!empty($param['status']) && $param['status'] ==  100){
            $where['o.status'] = ['in', '2,3,4,5,6'];
        }
        if (!empty($param['shop_id'])) $where['o.shop_id'] = ['eq', $param['shop_id']];
        if (!empty($param['order_type'])) $where['o.order_type'] = ['eq', $param['order_type']];
        if (!empty($param['title'])){
            $order_goods  = Db::name('order_goods')->where('title','like', '%'.$param['title'].'%')->find();
            $gift = Db::name('order o')->join('gift g','o.gift_id = g.id')->where('g.title','like','%'.$param['title'].'%')->find();
            $single  = Db::name('single')->where('cat_name','like', '%'.$param['title'].'%')->find();
            if(empty($order_goods) && empty($gift)){
               // $where['m.username|o.shop_order_number'] = ['like', '%'.$param['title'].'%'];
            }
            $where['m.username|o.shop_order_number|og.title|g.title|single.cat_name|sc.title'] = ['like', '%'.$param['title'].'%'];
//            if(!empty($order_goods)){
//                $where['o.order_type'] = ['eq', 1];
//                $where['og.title|o.username'] = ['like', '%'.$param['title'].'%'];
//            }
//            if(!empty($gift)){
//                $where['o.order_type'] = ['eq', 3];
//                $where['g.title|o.username'] = ['like', '%'.$param['title'].'%'];
//            }

        }


        $end_time = time();
        if (!empty($param['interval_timer'])) {
            $interval_timer = explode('/', $param['interval_timer']);
            $end_time = strtotime($interval_timer[1]);
            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];
        }
        $total_price = Db::name('order')
            ->alias('o')
            ->join('member m', 'o.member_id = m.id','LEFT')
            ->join('shop s', 'o.shop_id = s.id','LEFT')
            ->join('order_goods og','o.id = og.order_id','LEFT')
            ->join('gift g','g.id = o.gift_id','LEFT')
            ->join('single','single.id = o.single_parent_id','LEFT')
            ->join('system_card sc','o.system_card_id = sc.id','left')
            ->field('o.*,m.username,s.shop_title')
            ->fetchSql(false)
            ->where($where)->sum('amount');
        $sj_total_price = Db::name('order')
            ->alias('o')
            ->join('member m', 'o.member_id = m.id','LEFT')
            ->join('shop s', 'o.shop_id = s.id','LEFT')
            ->join('order_goods og','o.id = og.order_id','LEFT')
            ->join('gift g','g.id = o.gift_id','LEFT')
            ->join('single','single.id = o.single_parent_id','LEFT')
            ->join('system_card sc','o.system_card_id = sc.id','left')
            ->field('o.*,m.username,s.shop_title')
            ->fetchSql(false)
            ->where($where)->sum('sj');
        $total_count = Db::name('order o')
            ->join('member m', 'o.member_id = m.id','LEFT')
            ->join('order_goods og','o.id = og.order_id','left')
            ->join('shop s', 'o.shop_id = s.id','left')
            ->join('gift g','g.id = o.gift_id','LEFT')
            ->join('single','single.id = o.single_parent_id','LEFT')
            ->join('system_card sc','o.system_card_id = sc.id','left')
            ->field('o.*,m.username,s.shop_title')
            ->where($where)->count();
        $d_price = 0;
        $c_price = 0;
        $in_array = [];

        $data = \app\console\model\Order::alias('o')
            ->join('order_goods og','o.id = og.order_id','left')
            ->join('member m', 'o.member_id = m.id','left')
            ->join('shop s', 'o.shop_id = s.id','left')
            ->join('gift g','g.id = o.gift_id','LEFT')
            ->join('single','single.id = o.single_parent_id','LEFT')
            ->join('system_card sc','o.system_card_id = sc.id','left')
            ->field('o.system_card_id,o.t_type,o.gift_id,o.order_number,o.gift_id,o.shop_id,DATE_FORMAT(o.create_time,"%Y/%m/%d") create_time,o.id,o.order_type,o.single_parent_id,o.single_id,og.goods_id,o.amount,o.shop_order_number,o.username,o.status,o.refund_create_time,s.shop_title,o.shipping,o.is_read,o.order_number,s.shop_title')
            ->where($where)
            ->order('o.id','desc')
            ->paginate($pa, false, ['query' => $param])->each(function($item,$key) use($level,$end_time,&$d_price,&$c_price,&$in_array){
                if($item['status'] == 12 || $item['status'] == 10 || $item['status'] == 11){
                    $item->refund_create_time = Db::name('order_refund')->where('order_id',$item['id'])->value('create_time');
                }
                $item->year = explode('/',$item->create_time)[0];
                $item->month = explode('/',$item->create_time)[1];
                $item->pzrq = date('Y/n/j',$end_time);//凭证日期
                $item->kjnd = date('Y',$end_time);//会计年度
                $item->kjqj = date('n',$end_time);//会计期间
                $item->pzz = '记';//凭证字
                $item->pzh = '';//凭证号
                $item->d = 0;
                $item->c = '0';
                $item->pzzy = '';
                $item->subject_code = 0;
                $item->others = '';
                switch ($item->order_type){
                    case 1;
                        $dc = Db::name('shop_goods')->where('id',$item->goods_id)->value('dc');
                        $title = Db::name('shop_goods')->where('id',$item->goods_id)->value('title');
                        $item->pzzy = $title;
                        if($dc == 3){ //1借方 2贷方 为什么 这么判断呢？ 每次处理一个订单ID加进数组 订单有重复的时候?
                            $item->d = $item->amount;
                            if(!in_array($item->id,$in_array)){
                                $d_price += $item->amount;
                                $in_array[] = $item->id;
                            }
                        }else{
                            $item->c = $item->amount;
                            if(!in_array($item->id,$in_array)) {
                                $c_price += $item->amount;
                                $in_array[] = $item->id;
                            }
                        }
                        $item->subject_code = Db::name('shop_goods')->where('id',$item->goods_id)->value('code');
                        $item->others = 'ID:'.$item->goods_id.'-'.$title;
                        break;
                    case 2;
                        $dc = Db::name('single')->where('id',$item->single_id)->value('dc');
                        $parent_title = Db::name('single')->where('id',$item->single_parent_id)->value('cat_name');
                        $title = Db::name('single')->where('id',$item->single_id)->value('cat_name');
                        $item->pzzy = $title;
                        if($dc == 3){
                            $item->d = $item->amount;
                            $d_price += $item->amount;
                        }else{
                            $item->c = $item->amount;
                            $c_price += $item->amount;
                        }
                        $s_code1 = Db::name('single')->where('id',$item->single_parent_id)->value('code');
                        $s_code2 = Db::name('single')->where('id',$item->single_id)->value('code');
                        if($s_code1 && $s_code2){
                            $item->subject_code = $s_code1.'.'.$s_code2;
                        }else{
                            $item->subject_code  = '';
                        }

                        $item->others = $parent_title.'>'.$title;
                        break;
                    case 3;
                        if($item->t_type == 1){
                            $dc = Db::name('gift')->where('id',$item->gift_id)->value('dc');
                            $title = Db::name('gift')->where('id',$item->gift_id)->value('title');
                            $item->pzzy = $title;
                            if($dc == 3){
                                $item->d = $item->amount;
                                $d_price += $item->amount;
                            }else{
                                $item->c = $item->amount;
                                $c_price += $item->amount;
                            }
                            $item->subject_code = Db::name('gift')->where('id',$item->gift_id)->value('code');
                            $item->others = $title;
                        }
                        if ($item->t_type == 2) {
                            $dc = Db::name('system_card')->where('id', $item->system_card_id)->value('dc');
                            $title = Db::name('system_card')->where('id', $item->system_card_id)->value('title');
                            $item->pzzy = $title;
                            if ($dc == 3) {
                                $item->d = $item->amount;
                                $d_price += $item->amount;
                            } else {
                                $item->c = $item->amount;
                                $c_price += $item->amount;
                            }
                            $item->subject_code = Db::name('system_card')->where('id', $item->system_card_id)->value('code');
                            $item->others = $title;
                        }
                        break;
                }
                $item->subject_name = '';//科目名称
                $item->bbdm = 'RMB';//币别代码
                $item->bbmc = '人民币';//币别代码
                $item->amount = $item->amount;//原币金额
                $item->zd = '';//制单
                $item->sh = '';//审核
                $item->hz = 'NONE';//核准
                $item->cn = 'NONE';//出纳
                $item->jb = '';//经办
                $item->jsfs = '*';//结算方式
                $item->jsh = '';//结算号
                $item->sl = '0';//数量
                $item->sldw = '*';//数量单位
                $item->dj = '0';//单价
                $item->ckxx = '';//参考信息
                $item->ywrq = date('Y/n/j',$end_time);//业务日期  ....
                $item->wlyybh = '';//往来业务编号
                $item->ffs = 0;//附件数
                $item->xh = 1 ;//序号
                $item->xtmk = '';//系统模块
                $item->ywms = '';//业务描述
                $item->hl = 1;//汇率
                $item->flxh = $key+1;//分录序号
                $item->hsxm = '';//合算项目
                $item->gz = 0;//过账
                $item->jzpz = '';//机制凭证
                $item->xjll = '';//现金流量
            });

        if(isset($param['export'])){
            $header[0] = [
                'pzrq' => date('Y/n/j',$end_time),
                'kjnd' => date('Y',$end_time),
                'kjqj' => date('n',$end_time),
                'pzz' => '记',
                'pzh' => '',
                'subject_code' => '1001',
                'subject_name' => '',
                'bbdm' => 'RMB',
                'bbmc' => '人民币',
                'amount' => $total_price,
                'd' => $c_price,
                'c' => 0,
                'zd' => '',
                'sh' => '',
                'hz' => 'NONE',
                'cn' => 'NONE',
                'jb' => '',
                'jsfs' => '*',
                'jsh' => '',
                'pzzy' => '现金',
                'sl' => '0',
                'sldw' => '*',
                'dj' => 0,
                'ckxx' => '',
                'ywrq' => date('Y/n/j',$end_time),
                'wlyybh' => '',
                'ffs' => '0',
                'xh' => '1',
                'xtmk' => '',
                'ywms' => '',
                'hl' => '1',
                'flxh' => 0,
                'hsxm' => '',
                'gz' => '0',
                'jzpz' => '',
                'xjll' => '',
                'order_number' => '',
                'others' => ''
            ];
            //array_keys($header[0]);
            //$a=array("Volvo"=>"XC90","BMW"=>" ","Toyota"=>"Highlander");
            //print_r(array_keys($a));
            //halt(array_keys($header[0]));
            $array = array_merge($header,$data->toArray()['data']);
            //$this->push($array);
            //$this->exportExcel($array,'daochu',['amount']);
           $this->push($array);
        }

        return view('', ['data' => $data,
            'total_count' => $total_count,
            'total_price' => $total_price,
            'sj_total_price' => $sj_total_price,
        ]);
    }

    /**
     * 创建(导出)Excel数据表格
     * @param  array   $list        要导出的数组格式的数据
     * @param  string  $filename    导出的Excel表格数据表的文件名
     * @param  array   $indexKey    $list数组中与Excel表格表头$header中每个项目对应的字段的名字(key值)
     * @param  array   $startRow    第一条数据在Excel表格中起始行
     * @param  [bool]  $excel2007   是否生成Excel2007(.xlsx)以上兼容的数据表
     * 比如: $indexKey与$list数组对应关系如下:
     *     $indexKey = array('id','username','sex','age');
     *     $list = array(array('id'=>1,'username'=>'YQJ','sex'=>'男','age'=>24));
     */
    public function exportExcel($list, $filename, $indexKey = ['id','order_number'], $startRow = 1, $excel2007 = false)
    {
        //文件引入
        //require_once APP_ROOT.'/Api/excel/PHPExcel.php';
        //require_once APP_ROOT.'/Api/excel/PHPExcel/Writer/Excel2007.php';
        require_once '../extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        require_once '../extend/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';

        if (empty($filename)) $filename = time();
        if (!is_array($indexKey)) return false;

        $header_arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
        //初始化PHPExcel()
        $objPHPExcel = new \PHPExcel();

        //设置保存版本格式
        if ($excel2007) {
            $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
            $filename = $filename . '.xlsx';
        } else {
            $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
            $filename = $filename . '.xls';
        }

        //接下来就是写数据到表格里面去
        $objActSheet = $objPHPExcel->getActiveSheet();
        //$startRow = 1;
        foreach ($list as $row) {
            foreach ($indexKey as $key => $value) {
                //这里是设置单元格的内容
                $objActSheet->setCellValue($header_arr[$key] . $startRow, $row[$value]);
            }
            $startRow++;
        }

        // 下载这个表格，在浏览器输出
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=' . $filename . '');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }




    /**
     * @param int $pa
     * @return array|mixed|\think\response\View
     * 门店商品
     */
    public function goods($pa = 10)
    {
        $param = Request::instance()->param();
        $where = [];
        if (!empty($param['status'])){
            if($param['status'] == 2 || $param['status'] == 6){
                $where['o.status'] = ['in', $this->status];
            }else{
                $where['o.status'] = ['eq', $param['status']];
            }
        }
        if (!empty($param['interval_timer'])) {
            $interval_timer = explode('/', $param['interval_timer']);
            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];
        }
        if(!empty($param['shop_id'])) $where['o.shop_id'] = ['eq',$param['shop_id']];
        if(!empty($param['goods_id'])) $where['goods_id'] = ['eq',$param['goods_id']];
        if(!empty($param['title'])) $where['title|attribute'] = ['like','%'.$param['title'].'%'];
        $data = (new OrderGoods())
            ->alias('og')
            ->where($where)
            ->join('order o', 'og.order_id = o.id')
            ->join('shop s','o.shop_id = s.id')
            ->order('og.id','desc')
            ->paginate($pa, false, ['query' => $param]);
        $data = (new OrderGoods())
            ->alias('og')
            ->where($where)
            ->join('order o', 'og.order_id = o.id')
            ->join('shop s','o.shop_id = s.id')
            ->order('og.id','desc')
            ->field('og.*,s.*,o.*')
            ->paginate($pa, false, ['query' => $param]);
        $goods_list = OrderGoods::group('goods_id')->select();
        $total_count = (new OrderGoods())
            ->alias('og')
            ->where($where)
            ->join('order o', 'og.order_id = o.id')
            ->count();
        $t_data = (new OrderGoods())
            ->alias('og')
            ->where($where)
            ->join('order o', 'og.order_id = o.id')
            ->join('shop s','o.shop_id = s.id')
            ->order('og.id','desc')
            ->select();
        $total_price = 0;
        if(!empty($t_data)){
            foreach ($t_data as $v){
                $total_price += $v['price']*$v['number'];
            }
        }
        if(isset($param['export'])){
            $this->goods_push($data);
        }

        return view('', ['data' => $data,'total_price' => $total_price,'total_count' => $total_count,'goods_list' => $goods_list]);
    }

    /**
     * @param int $pa
     * @return array|mixed|\think\response\View
     * 礼包商品
     */
    public function gift($pa = 10)
    {

        $param = Request::instance()->param();
        $where = [];
        if (!empty($param['interval_timer'])) {
            $interval_timer = explode('/', $param['interval_timer']);
            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];
        }
        if (!empty($param['status'])){
            if($param['status'] == 2){
                $where['o.status'] = ['in', $this->status];
            }else{
                $where['o.status'] = ['eq', $param['status']];
            }
        }
        if (!empty($param['gift_id'])) $where['gift_id'] = ['eq', $param['gift_id']];
        if(!empty($param['title'])) $where['gift_id'] = ['eq',Db::name('gift')->where(['title' => ['like','%'.$param['title'].'%']])->value('id')];
        $data = (new Order())
            ->alias('o')
            ->where('order_type', 3)
            ->where($where)
            ->order('id', 'desc')
            ->paginate($pa, false, ['query' => $param])->each(function($item,$key){
                switch ($item->t_type){
                    case 1:
                    $item->t_type_text = '礼包';
                    break;
                    case 2:
                        $item->t_type_text = '会员卡';
                        $item->gift_id = $item->system_card_id;
                        $item->gift_title = $item->mc_title;
                        $item->gift_start_time = $item->mc_start_time;
                        $item->gift_end_time = $item->mc_end_time;
                        $item->sale_price = $item->amount;
                        break;
                }
            });
        $gift_list = (new Order())->alias('o')->join('gift g', 'o.gift_id = g.id','left')->group('g.id')->select();

        $total_count = (new Order())->alias('o')->where('order_type', 3)->where($where)->count();
        $total_price = (new Order())->alias('o')->where('order_type', 3)->where($where)->sum('sale_price');
        if(isset($param['export'])){
            $this->gift_push($data);
        }
        return view('', ['data' => $data, 'total_price' => $total_price, 'total_count' => $total_count, 'gift_list' => $gift_list]);
    }

    /**
     * @param int $pa
     * @return array|mixed|\think\response\View
     * 服务商品
     */
    public function service($pa = 10)
    {
        $param = Request::instance()->param();
        $where = [];
        if (!empty($param['interval_timer'])) {
            $interval_timer = explode('/', $param['interval_timer']);
            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];
        }
        if (!empty($param['status'])){
            if($param['status'] == 2){
                $where['o.status'] = ['in', $this->status];
            }else{
                $where['o.status'] = ['eq', $param['status']];
            }
        }
        if (!empty($param['single_parent_id'])) $where['single_parent_id'] = ['eq', $param['single_parent_id']];
        if(!empty($param['shop_id'])) $where['o.shop_id'] = ['eq',$param['shop_id']];
        if (!empty($param['title'])){
            $where['single_parent_id|single_id'] = ['eq', Db::name('single')->where('cat_name','like','%'.$param['title'].'%')->value('id')];
        }
        $data = (new Order())
            ->alias('o')
            ->join('single s','o.single_parent_id = s.id','left')
            ->join('shop sp','o.shop_id = sp.id','left')
            ->where('order_type', 2)
            ->where($where)
            ->order('o.id', 'desc')
            ->field('s.*,o.*,sp.shop_title')
            ->paginate($pa, false, ['query' => $param])->each(function($item,$key){
                $item->two_level = Db::name('single')->where('id',$item->single_id)->value('cat_name');
            });
        $single_list = (new Order())->alias('o')->join('single s', 'o.single_parent_id = s.id')->group('o.single_parent_id')->select();
        $total_count = (new Order())->alias('o')->where('order_type', 2)->where($where)->count();
        $total_price = (new Order())->alias('o')->where('order_type', 2)->where($where)->sum('amount');
        $sj_total_price = (new Order())->alias('o')->where('order_type', 2)->where($where)->sum('sj');
        if(isset($param['export'])){

            $this->service_push($data);
        }

        return view('', ['data' => $data, 'total_price' => $total_price, 'total_count' => $total_count, 'single_list' => $single_list,'sj_total_price' => $sj_total_price]);
    }


    public function order()
    {
        $param = Request::instance()->param();
        $data = Db::name('order')
            ->alias('o')
            ->where('shop_order_number', $param['shop_order_number'])
            ->paginate(10, false, ['query' => $param]);
        return view('', []);
    }

    public function daochu()
    {

        $param = Request::instance()->param();
        $level = \session('role_id');//当前登录的管理员界别 5 7 8 9 10
        $page = Request::instance()->param('page',10);
        $where = [];
        $auding_status = Session::get('auding_status');
        if($auding_status == 1){
            $where['shop_id'] = ['eq',\session('shop_id')];
        }


        if (!empty($param['status'])) $where['o.status'] = ['eq', $param['status']];
        if (!empty($param['shop_id'])) $where['o.shop_id'] = ['eq', $param['shop_id']];
        if (!empty($param['title'])) $where['m.username|o.shop_order_number|o.order_number'] = ['eq', $param['title']];

        $end_time = time();
        if (!empty($param['interval_timer'])) {
            $interval_timer = explode('/', $param['interval_timer']);
            $end_time = strtotime($interval_timer[1]);
            $where['o.create_time'] = ['between', [$interval_timer[0], $interval_timer[1]]];
        }


        $data = \app\console\model\Order::alias('o')
            ->join('order_goods og','o.id = og.order_id','left')
            ->join('member m', 'o.member_id = m.id','left')
            ->join('shop s', 'o.shop_id = s.id','left')
            ->field('o.order_number,o.gift_id,o.shop_id,DATE_FORMAT(o.create_time,"%Y/%m/%d") create_time,o.id,o.order_type,o.single_parent_id,o.single_id,og.goods_id,o.amount,o.shop_order_number,m.username,o.status,o.refund_create_time,s.shop_title,o.shipping,o.is_read,o.order_number')
            ->where($where)
            ->fetchSql(false)
            ->order('o.id','desc')
            ->paginate(10000, false, ['query' => $param])->each(function($item,$key) use($level,$end_time){
                if($item['status'] == 12 || $item['status'] == 10 || $item['status'] == 11){
                    $item->refund_create_time = Db::name('order_refund')->where('order_id',$item['id'])->value('create_time');
                }
                $item->year = explode('/',$item->create_time)[0];
                $item->month = explode('/',$item->create_time)[1];
                $item->pzrq = date('Y/m/d',$end_time);//凭证日期
                $item->kjnd = date('Y',$end_time);//会计年度
                $item->kjqj = date('m',$end_time);//会计期间
                $item->pzz = '记';//凭证字
                $item->pzh = $key;//凭证号
                $item->d = '';
                $item->c = '';
                $item->pzzy = '';
                switch ($item->order_type){
                    case 1;
                        $dc = Db::name('shop_goods')->where('id',$item->goods_id)->value('dc');
                        $title = Db::name('shop_goods')->where('id',$item->goods_id)->value('title');
                        $item->pzzy = $title;
                        if($dc == 1){
                            $item->d = $item->amount;
                        }else{
                            $item->c = $item->amount;
                        }
                        $item->subject_code = Db::name('shop_goods')->where('id',$item->goods_id)->value('code');
                        break;
                    case 2;
                        $dc = Db::name('single')->where('id',$item->single_id)->value('dc');
                        $title = Db::name('single')->where('id',$item->single_id)->value('cat_name');
                        $item->pzzy = $title;
                        if($dc == 1){
                            $item->d = $item->amount;
                        }else{
                            $item->c = $item->amount;
                        }
                        $item->subject_code = Db::name('single')->where('id',$item->single_parent_id)->value('code').'.'.Db::name('single')->where('id',$item->single_id)->value('code');
                        break;
                    case 3;
                        $dc = Db::name('gift')->where('id',$item->gift_id)->value('dc');
                        $title = Db::name('gift')->where('id',$item->gift_id)->value('title');
                        $item->pzzy = $title;
                        if($dc == 1){
                            $item->d = $item->amount;
                        }else{
                            $item->c = $item->amount;
                        }
                        $item->subject_code = Db::name('gift')->where('id',$item->gift_id)->value('code');
                        break;
                }
                $item->subject_name = '';//科目名称
                $item->bbdm = 'RMB';//币别代码
                $item->bbmc = '人民币';//币别代码
                $item->amount = $item->amount;//原币金额
                $item->zd = '';//制单
                $item->sh = '';//审核
                $item->hz = 'NONE';//核准
                $item->cn = 'NONE';//出纳
                $item->jb = '';//经办
                $item->jsfs = '*';//结算方式
                $item->jsh = '';//结算号
                $item->sl = '0';//数量
                $item->sldw = '*';//数量单位
                $item->dj = 0;//单价
                $item->ckxx = '';//参考信息
                $item->ywrq = '';//业务日期  ....
                $item->wlyybh = '';//往来业务编号
                $item->ffs = 0;//附件数
                $item->xh = 1 ;//序号
                $item->xtmk = '';//系统模块
                $item->ywms = '';//业务描述
                $item->hl = 1;//汇率
                $item->flxh = '';//分录序号
                $item->hsxm = '';//合算项目
                $item->gz = 0;//过账
                $item->jzpz = '';//机制凭证
                $item->xjll = '';//现金流量
                $item->order_number = '';//现金流量

            });

        if(isset($param['export'])){

            $this->push($data);
        }

    }

    public function push($data, $name = 'KingDee')
    {

        set_time_limit(0);

        ini_set('memory_limit', '1024M');

        error_reporting(E_ALL);

        date_default_timezone_set('Asia/Shanghai');



        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');

        $objPHPExcel = new \PHPExcel();



        /*以下是一些设置 ，什么作者  标题啊之类的*/
        $objPHPExcel->getActiveSheet(0)->getDefaultRowDimension()->setRowHeight(12);
        $objPHPExcel->getProperties()->setCreator("管理员")

            ->setLastModifiedBy("管理员")

            ->setTitle("电话本导出")

            ->setSubject("数据EXCEL导出")

            ->setDescription("备份数据")

            ->setKeywords("excel")

            ->setCategory("result file");
        $objPHPExcel->setActiveSheetIndex()

            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
//            ->setCellValue('A1', '凭证日期')
//            ->setCellValue('B1', '会计年度')
//            ->setCellValue('C1', '会计期间')
//            ->setCellValue('D1', '凭证字')
//            ->setCellValue('E1', '凭证号')
//            ->setCellValue('F1', '科目代码')
//            ->setCellValue('G1', '科目名称')
//            ->setCellValue('H1', '币别代码')
//            ->setCellValue('I1', '币别名称')
//            ->setCellValue('J1', '原币金额')
//            ->setCellValue('K1', '借方')
//            ->setCellValue('L1', '贷方')
//            ->setCellValue('M1', '制单')
//            ->setCellValue('N1', '审核')
//            ->setCellValue('O1', '核准')
//            ->setCellValue('P1', '出纳')
//            ->setCellValue('Q1', '经办')
//            ->setCellValue('R1', '结算方式')
//            ->setCellValue('S1', '结算号')
//            ->setCellValue('T1', '凭证摘要')
//            ->setCellValue('U1', '数量')
//            ->setCellValue('V1', '数量单位')
//            ->setCellValue('W1', '单价')
//            ->setCellValue('X1', '参考信息')
//            ->setCellValue('Y1', '业务日期')
//            ->setCellValue('Z1', '往来业务编号')
//            ->setCellValue('AA1', '附件数')
//            ->setCellValue('AB1', '序号')
//            ->setCellValue('AC1', '系统模块')
//            ->setCellValue('AD1', '业务描述')
//            ->setCellValue('AE1', '汇率')
//            ->setCellValue('AF1', '分录序号')
//            ->setCellValue('AG1', '核算项目')
//            ->setCellValue('AH1', '过账')
//            ->setCellValue('AI1', '机制凭证')
//            ->setCellValue('AJ1', '现金流量')

            ->setCellValue('A1', 'FDate')
            ->setCellValue('B1', 'FYear')
            ->setCellValue('C1', 'FPeriod')
            ->setCellValue('D1', 'FGroupID')
            ->setCellValue('E1', 'FNumber')
            ->setCellValue('F1', 'FAccountNum')
            ->setCellValue('G1', 'FAccountName')
            ->setCellValue('H1', 'FCurrencyNum')
            ->setCellValue('I1', 'FCurrencyName')
            ->setCellValue('J1', 'FAmountFor')
            ->setCellValue('K1', 'FDebit')
            ->setCellValue('L1', 'FCredit')
            ->setCellValue('M1', 'FPreparerID')
            ->setCellValue('N1', 'FCheckerID')
            ->setCellValue('O1', 'FApproveID')
            ->setCellValue('P1', 'FCashierID')
            ->setCellValue('Q1', 'FHandler')
            ->setCellValue('R1', 'FSettleTypeID')
            ->setCellValue('S1', 'FSettleNo')
            ->setCellValue('T1', 'FExplanation')
            ->setCellValue('U1', 'FQuantity')
            ->setCellValue('V1', 'FMeasureUnitID')
            ->setCellValue('W1', 'FUnitPrice')
            ->setCellValue('X1', 'FReference')
            ->setCellValue('Y1', 'FTransDate')
            ->setCellValue('Z1', 'FTransNo')
            ->setCellValue('AA1', 'FAttachments')
            ->setCellValue('AB1', 'FSerialNum')
            ->setCellValue('AC1', 'FObjectName')
            ->setCellValue('AD1', 'FParameter')
            ->setCellValue('AE1', 'FExchangeRate')
            ->setCellValue('AF1', 'FEntryID')
            ->setCellValue('AG1', 'FItem')
            ->setCellValue('AH1', 'FPosted')
            ->setCellValue('AI1', 'FInternalInd')
            ->setCellValue('AJ1', 'FCashFlow')
//            ->setCellValue('AK1', '订单编号')
//            ->setCellValue('AL1', '商品其他信息')
        ;
        //

        $sheet1 = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ'];

        $total = count($data);
        for ($a = 1; $a <= $total+1; $a++) {
            for ($x = 0; $x < count($sheet1); $x++) {
                $objPHPExcel->getActiveSheet(0)->getStyle($sheet1[$x] . $a)->applyFromArray(
                    array(
                        'font' => array(
                            'bold' => false,
                            'size' => 10,
                            'name' => '宋体',
                        ),

                        'alignment' => array(
                            'horizontal' =>
                                \PHPExcel_Style_Alignment::HORIZONTAL_LEFT

                        )

                    )

                );

            }
        }




        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/



        $n = 0;

        foreach ($data as $v) {

            $num = $n + 2;
            $objPHPExcel->getactivesheet()

                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, $v['pzrq'])
                ->setCellValue('B' . $num, $v['kjnd'])
                ->setCellValue('C' . $num, $v['kjqj'])
                ->setCellValue('D' . $num, $v['pzz'])
                ->setCellValue('E' . $num, $v['pzh'])
                ->setCellValue('F' . $num, $v['subject_code'])
                ->setCellValue('G' . $num, $v['subject_name'])
                ->setCellValue('H' . $num, $v['bbdm'])
                ->setCellValue('I' . $num, $v['bbmc'])
                ->setCellValue('J' . $num, $v['amount'])
                ->setCellValue('K' . $num, $v['d'])
                ->setCellValue('L' . $num, $v['c'])
                ->setCellValue('M' . $num, $v['zd'])
                ->setCellValue('N' . $num, $v['sh'])
                ->setCellValue('O' . $num, $v['hz'])
                ->setCellValue('P' . $num, $v['cn'])
                ->setCellValue('Q' . $num, $v['jb'])
                ->setCellValue('R' . $num, $v['jsfs'])
                ->setCellValue('S' . $num, $v['jsh'])
                ->setCellValue('T' . $num, $v['pzzy'])
                ->setCellValue('U' . $num, $v['sl'])
                ->setCellValue('V' . $num, $v['sldw'])
                ->setCellValue('W' . $num, $v['dj'])
                ->setCellValue('X' . $num, $v['ckxx'])
                ->setCellValue('Y' . $num, $v['ywrq'])
                ->setCellValue('Z' . $num, $v['wlyybh'])
                ->setCellValue('AA' . $num, $v['ffs'])
                ->setCellValue('AB' . $num, $v['xh'])
                ->setCellValue('AC' . $num, $v['xtmk'])
                ->setCellValue('AD' . $num, $v['ywms'])
                ->setCellValue('AE' . $num, $v['hl'])
                ->setCellValue('AF' . $num, $v['flxh'])
                ->setCellValue('AG' . $num, $v['hsxm'])
                ->setCellValue('AH' . $num, $v['gz'])
                ->setCellValue('AI' . $num, $v['jzpz'])
                ->setCellValue('AJ' . $num, $v['xjll'])
//                ->setCellValue('AK' . $num, $v['order_number'])
//                ->setCellValue('AL' . $num, $v['others'])

            ;

            $n++;

        }

        $objPHPExcel->getActiveSheet(0)->setTitle('Page1');
        $objPHPExcel->createSheet();
        $objPHPExcel->setActiveSheetIndex(1);
        $objPHPExcel->getActiveSheet()->setTitle('t_Schema');
        $arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
        $j = 1;
        foreach (cache('excel_data') as $kk => $vv){
            $objPHPExcel->getactivesheet()->setCellValue('A' . $j, $vv[0])
                ->setCellValue('B' . $j, $vv['1'])
                ->setCellValue('C' . $j, $vv['2'])
                ->setCellValue('D' . $j, $vv['3'])
                ->setCellValue('E' . $j, $vv['4'])
                ->setCellValue('F' . $j, $vv['5'])
                ->setCellValue('G' . $j, $vv['6'])
                ->setCellValue('H' . $j, $vv['7'])
                ->setCellValue('I' . $j, $vv['8'])
                ->setCellValue('J' . $j, $vv['9'])
                ->setCellValue('K' . $j, $vv['10'])
                ->setCellValue('L' . $j, $vv['11'])
                ->setCellValue('M' . $j, $vv['12'])
                ->setCellValue('N' . $j, $vv['13'])
                ->setCellValue('O' . $j, $vv['14'])
                ->setCellValue('P' . $j, $vv['15'])
                ->setCellValue('Q' . $j, $vv['16'])
                ->setCellValue('R' . $j, $vv['17'])
                ->setCellValue('S' . $j, $vv['18'])
                ->setCellValue('T' . $j, $vv['19']);
            $j++;
        }

        $sheet1 = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ'];
        //$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('20');

        $objPHPExcel->getActiveSheet(1)->getDefaultRowDimension()->setRowHeight(12);

        $total_2 = count(session('excel_data'));
        for ($aa = 1; $aa <= $total_2; $aa++) {

            for ($xx = 0; $xx < count($sheet1); $xx++) {
                $objPHPExcel->getActiveSheet()->getStyle($sheet1[$xx] . $aa)->applyFromArray(
                    array(
                        'font' => array(
                            'bold' => false,
                            'size' => 10,
                            'name' => '宋体',
                            'height' => 30
                        ),
                        'alignment' => array(
                            'horizontal' =>
                                \PHPExcel_Style_Alignment::HORIZONTAL_LEFT

                        )

                    )

                );

            }
        }


        $objPHPExcel->setActiveSheetIndex(0);

        ob_end_clean();

//        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');
//        header('Cache-Control: max-age=0');
//        header('Content-Type:application/vnd.ms-excel');
//        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        header('Content-Disposition: attachment;charset=gb2312;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //设置保存版本格式
//        if($excel2007){
//            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//            $filename = $filename.'.xlsx';
//        }else{
//            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
//            $filename = $filename.'.xls';
//        }
//        ---------------------
//作者：weixin_39696369
//来源：CSDN
//原文：https://blog.csdn.net/weixin_39696369/article/details/78646494
//版权声明：本文为博主原创文章，转载请附上博文链接！
        //xlsx Excel2007 xls
        //需要导出xls的话，用 header('Content-Type:application/vnd.ms-excel');
        //需要导出xlsx的话，用 header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        //xls和xlsx的区别的话，是储存数据量大小的问题，xls只可以保存大概5、6w数据，xlsx可以100w左右

        $objWriter->save('php://output');
        exit;
    }


    /**
     * 导入EXCEL
     */
    public function excelToArray()
    {
        // dirname(__FILE__) .
        $rule = require_once '../extend/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php';

        // import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');

        //$objPHPExcel = new \PHPExcel();
        //加载excel文件
        $filename = './KingDee (123).xlsx';

        $objPHPExcelReader = \PHPExcel_IOFactory::load($filename);
        $sheet = $objPHPExcelReader->getSheet(0);        // 读取第一个工作表(编号从 0 开始)
        $highestRow = $sheet->getHighestRow();           // 取得总行数
        $highestColumn = $sheet->getHighestColumn();     // 取得总列数

        $arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
        // 一次读取一列
        $res_arr = array();
        for ($row = 1; $row <= $highestRow; $row++) {
            $row_arr = array();
            for ($column = 0; $arr[$column] != 'U'; $column++) {
                $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                $row_arr[] = $val;
            }

            $res_arr[] = $row_arr;
        }
        halt($res_arr);
        Cache::set('excel_data', $res_arr);

        //return $res_arr;

    }


    public function goods_push($data, $name = '商品导出')
    {

        set_time_limit(0);
        ini_set('memory_limit', '1024M');
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');

        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');
        $objPHPExcel = new \PHPExcel();



        /*以下是一些设置 ，什么作者  标题啊之类的*/

        $objPHPExcel->getProperties()->setCreator("管理员")

            ->setLastModifiedBy("管理员")

            ->setTitle("电话本导出")

            ->setSubject("数据EXCEL导出")

            ->setDescription("备份数据")

            ->setKeywords("excel")

            ->setCategory("result file");

        $objPHPExcel->setActiveSheetIndex(0)

            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
            ->setCellValue('A1', '订单号')
            ->setCellValue('B1', '门店名称')
            ->setCellValue('C1', '商品ID')
            ->setCellValue('D1', '商品标题')
            ->setCellValue('E1', '商品价格')
            ->setCellValue('F1', '商品属性')
            ->setCellValue('G1', '商品数量')
            ->setCellValue('H1', '创建日期')

        ;

        $sheet1 = [];


        for ($x = 0; $x < count($sheet1); $x++) {

            $objPHPExcel->getActiveSheet()->getStyle($sheet1[$x] . '1')->applyFromArray(

                array(

                    'font'      => array(

                        'bold' => true

                    ),

                    'alignment' => array(

                        'horizontal' =>

                            \PHPExcel_Style_Alignment::HORIZONTAL_CENTER

                    )

                )

            );

        }



        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/



        $n = 0;

        foreach ($data as $v) {

            $num = $n + 2;
            $objPHPExcel->setActiveSheetIndex(0)

                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, $v['shop_order_number'])
                ->setCellValue('B' . $num, $v['shop_title'])
                ->setCellValue('C' . $num, $v['goods_id'])
                ->setCellValue('D' . $num, $v['title'])
                ->setCellValue('E' . $num, $v['price'])
                ->setCellValue('F' . $num, $v['attribute'])
                ->setCellValue('G' . $num, $v['number'])
                ->setCellValue('H' . $num, $v['create_time'])

            ;



            $n++;

        }



        $objPHPExcel->getActiveSheet(0)->setTitle('商品导出');

        $objPHPExcel->setActiveSheetIndex(0);



        ob_end_clean();

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        $objWriter->save('php://output');
        exit;
    }

    public function gift_push($data, $name = '礼包导出')
    {

        set_time_limit(0);
        ini_set('memory_limit', '1024M');
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');

        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');
        $objPHPExcel = new \PHPExcel();


        /*以下是一些设置 ，什么作者  标题啊之类的*/
        $objPHPExcel->getProperties()->setCreator("管理员")
            ->setLastModifiedBy("管理员")
            ->setTitle("电话本导出")
            ->setSubject("数据EXCEL导出")
            ->setDescription("备份数据")
            ->setKeywords("excel")
            ->setCategory("result file");
        $objPHPExcel->setActiveSheetIndex(0)
            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
            ->setCellValue('A1', '订单号')
            ->setCellValue('B1', '礼包ID')
            ->setCellValue('C1', '礼包标题')
            ->setCellValue('D1', '开始时间')
            ->setCellValue('E1', '结束时间')
            ->setCellValue('F1', '售卖价格')
            ->setCellValue('G1', '创建时间')

        ;

        $sheet1 = [];


        for ($x = 0; $x < count($sheet1); $x++) {

            $objPHPExcel->getActiveSheet()->getStyle($sheet1[$x] . '1')->applyFromArray(

                array(

                    'font'      => array(

                        'bold' => true

                    ),

                    'alignment' => array(

                        'horizontal' =>

                            \PHPExcel_Style_Alignment::HORIZONTAL_CENTER

                    )

                )

            );

        }



        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/



        $n = 0;

        foreach ($data as $v) {

            $num = $n + 2;
            $objPHPExcel->setActiveSheetIndex(0)

                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, $v['order_number'])
                ->setCellValue('B' . $num, $v['gift_id'])
                ->setCellValue('C' . $num, $v['gift_title'])
                ->setCellValue('D' . $num, $v['gift_start_time'])
                ->setCellValue('E' . $num, $v['gift_end_time'])
                ->setCellValue('F' . $num, $v['sale_price'])
                ->setCellValue('G' . $num, $v['create_time'])

            ;



            $n++;

        }



        $objPHPExcel->getActiveSheet(0)->setTitle('礼包导出');

        $objPHPExcel->setActiveSheetIndex(0);



        ob_end_clean();

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        $objWriter->save('php://output');
        exit;
    }

    public function service_push($data, $name = '服务导出')
    {

        set_time_limit(0);
        ini_set('memory_limit', '1024M');
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');

        import('phpoffice.phpexcel.Classes.PHPExcel', EXTEND_PATH, '.php');
        $objPHPExcel = new \PHPExcel();


        /*以下是一些设置 ，什么作者  标题啊之类的*/
        $objPHPExcel->getProperties()->setCreator("管理员")
            ->setLastModifiedBy("管理员")
            ->setTitle("电话本导出")
            ->setSubject("数据EXCEL导出")
            ->setDescription("备份数据")
            ->setKeywords("excel")
            ->setCategory("result file");
        $objPHPExcel->setActiveSheetIndex(0)
            //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
            ->setCellValue('A1', '订单号')
            ->setCellValue('B1', '门店名称')
            ->setCellValue('C1', '服务一级类')
            ->setCellValue('D1', '服务二级类')
            ->setCellValue('E1', '订单总额')
            ->setCellValue('F1', '运费金额')
            ->setCellValue('G1', '创建日期')
        ;

        $sheet1 = [];


        for ($x = 0; $x < count($sheet1); $x++) {

            $objPHPExcel->getActiveSheet()->getStyle($sheet1[$x] . '1')->applyFromArray(

                array(

                    'font'      => array(

                        'bold' => true

                    ),

                    'alignment' => array(

                        'horizontal' =>

                            \PHPExcel_Style_Alignment::HORIZONTAL_CENTER

                    )

                )

            );

        }



        /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/



        $n = 0;

        foreach ($data as $v) {

            $num = $n + 2;
            $objPHPExcel->setActiveSheetIndex(0)

                //Excel的第A列，shop_name是你查出数组的键值，下面以此类推
                ->setCellValue('A' . $num, $v['order_number'])
                ->setCellValue('B' . $num, $v['shop_title'])
                ->setCellValue('C' . $num, $v['cat_name'])
                ->setCellValue('D' . $num, $v['two_level'])
                ->setCellValue('E' . $num, $v['amount'])
                ->setCellValue('F' . $num, $v['freight'])
                ->setCellValue('G' . $num, $v['create_time'])

            ;



            $n++;

        }



        $objPHPExcel->getActiveSheet(0)->setTitle('服务导出');

        $objPHPExcel->setActiveSheetIndex(0);



        ob_end_clean();

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;charset=utf-8;filename="' . $name . '.xls"');

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        $objWriter->save('php://output');
        exit;
    }
}